﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using CI.DAL;
using CI.Interfaces;
using CI.Mesopotamia;
using CI;
using CI.Misc;
using CI.Organism;
using CI.WebService;
using MesopotamiaDCProxy.GridDBTableAdapters;


namespace MesopotamiaDCProxy
{
    /// <summary>
    /// Helper class to save and load scenario's primarly
    /// </summary>
    public class DBHelper
    {
        #region Table Adapters, Views, Query's
        private readonly GridDBTableAdapters.QueriesTableAdapter _qta = new GridDBTableAdapters.QueriesTableAdapter();
        private readonly GridDBTableAdapters.vw_scenariosTableAdapter _vsta = new GridDBTableAdapters.vw_scenariosTableAdapter();
        private readonly GridDBTableAdapters.vw_requirementsTableAdapter _vrta = new GridDBTableAdapters.vw_requirementsTableAdapter();
        private readonly GridDBTableAdapters.vw_scenarioRequirementLocationsTableAdapter _vsrlta = new GridDBTableAdapters.vw_scenarioRequirementLocationsTableAdapter();
        private readonly GridDBTableAdapters.vw_scenarioOrganismsTableAdapter _vsota = new GridDBTableAdapters.vw_scenarioOrganismsTableAdapter();
        private readonly GridDBTableAdapters.vw_locationTableAdapter _vlta = new GridDBTableAdapters.vw_locationTableAdapter();
        private readonly GridDBTableAdapters.vw_organismCellsTableAdapter _vocta = new GridDBTableAdapters.vw_organismCellsTableAdapter();
        private readonly GridDBTableAdapters.vw_synapsesTableAdapter _vsynta = new GridDBTableAdapters.vw_synapsesTableAdapter();
        private readonly GridDBTableAdapters.vw_chromosomesTableAdapter _vcta = new GridDBTableAdapters.vw_chromosomesTableAdapter();
        private readonly GridDBTableAdapters.vw_genesTableAdapter _vgta = new GridDBTableAdapters.vw_genesTableAdapter();
        private readonly GridDBTableAdapters.vw_geneRequirementsTableAdapter _vgrta = new GridDBTableAdapters.vw_geneRequirementsTableAdapter();
        private readonly GridDBTableAdapters.usp_getOrganismsByScenarioIDTableAdapter _uspgosta = new usp_getOrganismsByScenarioIDTableAdapter();
        #endregion

        #region Const Types
        private const string AMOEBA = "CI.Organism.Amoeba";
        private const string IROBOT = "CI.Organism.iRobot";
        private const string STEPNEURON = "CI.Organism.StepNeuron";
        private const string SNS = "CI.Organism.SimpleNeuralSystem";
        private const string SIMPLESYNAPSE = "CI.Organism.SimpleSynapse";
        private const string USEITORLOSEIT = "CI.Organism.UseItOrLoseIt";
        private const string GENECELLULAR = "CI.Organism.GeneCellular";
        private const string GENEASEXUALREPRODUCTION = "CI.Organism.GeneReproductionAsexual";
        private const string REQHBOFFSET = "CI.Organism.HBOffsetTime";
        private const string REQHBOFFSETCELL = "CI.Organism.HBOffsetTimeCell";
        private const string REQIOSYNAPSEVALUE = "CI.Organism.IOSynapseValue";
        private const string CTEQUAL = "Equal";
        private const string CTGT = "GreaterThan";
        private const string CTLT = "LessThan";
        private const string CTUNKNOWN = "Unknown";
        private const string ATADDCELL = "AddCell";
        private const string ATDELCELL = "DelCell";
        private const string ATREMCELL = "RemoveCell";
        private const string ATNOTAPP = "NotApplicable";
        private const string ATUNKNOWN = "Unknown";
        #endregion

        /// <summary>
        /// Delete a scenario by name
        /// </summary>
        /// <param name="scenarioName"></param>
        /// <returns></returns>
        public bool DeleteScenario(string scenarioName)
        {
            // todo : setup handling for return type
            object test = _qta.usp_deleteScenario(scenarioName);
            return true;
        }

        /// <summary>
        /// Get the list of scenarios
        /// </summary>
        /// <returns></returns>
        public List<string> GetScenarioList(string hostVersion)
        {
            var results = new List<string>();

            // get the data from the db
            var vsdt = new GridDB.vw_scenariosDataTable();
            try
            {
                _vsta.Fill(vsdt);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vsdt, ex);
            }

            for (int i = 0; i < vsdt.Rows.Count; i++)
            {
                results.Add(vsdt[i].ScenarioName);
            }

            return results;
        }

        public ScenarioOrganismCollection GetScenarioOrganisms(string scenarioName, int startIndex, int endIndex)
        {
            var scenarioOrganisms = new ScenarioOrganismCollection { Organisms = new List<ScenarioOrganism>() };

            var scenarioId = GetScenarioID(scenarioName);

            var gosta = new GridDB.usp_getOrganismsByScenarioIDDataTable();

            try
            {
                _uspgosta.Fill(gosta, scenarioId, startIndex, endIndex);
            }
            catch (Exception ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", gosta, ex);
            }
            _uspgosta.GetData(scenarioId, startIndex, endIndex);

            foreach (var dataRow in gosta)
            {
                var scenarioOrganism = new ScenarioOrganism
                                           {
                                               Guid = new Guid(dataRow.GUID),
                                               ChromosomesCount = dataRow.Chromsomes,
                                               DateTimeStamp = dataRow.DateTimeStamp,
                                               Energy = dataRow.Energy,
                                               Fitness = dataRow.Fitness,
                                               GenesCount = dataRow.Genes,
                                               InitCIET = dataRow.InitCIET,
                                               NeuronsCount = dataRow.Neurons,
                                               SynapseCount = dataRow.Synapses
                                           };
                scenarioOrganisms.Organisms.Add(scenarioOrganism);

            }
            return scenarioOrganisms;
        }


        #region Save

        /// <summary>
        /// save the scenario passed to it
        /// </summary>
        /// <param name="scenario"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int SaveScenario(IScenario scenario, int userId)
        {
            // save the main scenario bit
            var scenarioResult = int.Parse(_qta.usp_saveScenario(scenario.ScenarioName, scenario.ManifestFile, scenario.HostingEnvironmentVersionInformation, scenario.ScenarioDescription, scenario.FitnessFunction, scenario.SimulationRestrictions, scenario.BoundryXRadiusMax, scenario.BoundryYRadiusMax, scenario.BoundryZRadiusMax, scenario.MaxSaveCount, scenario.MaxLoadCount).ToString());

            // save the requirments
            foreach (IScenarioEntityRequirements entityRequirements in scenario.RequiredOrganisms)
            {
                // add the requirement
                var requirementResult = int.Parse(_qta.usp_saveRequirement(scenarioResult, entityRequirements.OrganismType.GetType().ToString(), entityRequirements.UseFallBackOnly).ToString());

                foreach (ILocation location in entityRequirements.Locations)
                {
                    // add the locations
                    int locationResult = int.Parse(_qta.usp_saveLocation(location.XAxis, location.YAxis, location.ZAxis).ToString());

                    // add the requirement to location binding
                    _qta.usp_saveRequirementLocation(requirementResult, locationResult, location.XRadius, location.YRadius, location.ZRadius);
                }
            }

            // save the fall back orgs
            SaveScenarioFallBackOrganisms(scenario, scenarioResult, userId);

            // return the sceanrio ID added
            return scenarioResult;
        }

        /// <summary>
        /// Save the scenario
        /// </summary>
        /// <param name="scenario">scenario to save</param>
        /// <param name="scenarioId">DB id it has</param>
        /// <param name="userId"></param>
        private void SaveScenarioFallBackOrganisms(IScenario scenario, int scenarioId, int userId)
        {
            // loop through each organism
            foreach (IOrganism organism in scenario.FallBackOrganisms)
            {
                // as we are saving the scenario set the fitness to all of them to be 0
                organism.FitnessValue = 0;

                // save the organism
                int organismID = SaveOrganism(organism, scenario.ScenarioName, userId);

                // add the scenario to fallback organisms
                int scenarioOrganismID = int.Parse(_qta.usp_saveScenarioOrganism(scenarioId, organismID).ToString());

            }
        }

        /// <summary>
        /// Save the organism
        /// </summary>
        /// <param name="organism"></param>
        /// <param name="scenarioName"></param>
        /// <param name="userID"></param>
        private int SaveOrganism(IOrganism organism, string scenarioName, int userID)
        {
            // mock physical hosting environment
            var hostingEnvironmentPhysical = new MockHostingEnvironmentPhysical();

            // if not physical hosting environment which is needed for location finding then set one
            if (organism.HostingEnvironment.HostingEnvironmentPhysical == null)
                organism.HostingEnvironment.HostingEnvironmentPhysical = hostingEnvironmentPhysical;

            // save the location
            int locationID = int.Parse(_qta.usp_saveLocation(organism.Location.XAxis, organism.Location.YAxis, organism.Location.ZAxis).ToString());

            // set the neural system type, can be empty
            string neuralSystemTypeName = "NULL";
            // if it has one then save it
            if (organism.NeuralSystem != null)
                neuralSystemTypeName = organism.NeuralSystem.GetType().ToString();

            // add the organism to the organisms table via stored proc
            int organismID = int.Parse(_qta.usp_saveOrganism(organism.Cells.FindPhysicalZygote().GetType().ToString(), neuralSystemTypeName, scenarioName, userID, organism.FitnessValue, organism.CIET, organism.Guid.ToString(), Convert.ToInt32(organism.InitEnergy), locationID, organism.Energy, organism.Location.XRadius, organism.Location.YRadius, organism.Location.ZRadius, organism.InitCIET).ToString());

            // new dictionary of synapes
            var synapses = new Dictionary<Guid, ISynapse>();
            // dictionary of neurons cell id in db and their guids
            var neurons = new Dictionary<Guid, int>();
            var SynapseIOs = new Dictionary<Guid, int>();

            // save the cells     
            foreach (ICell cell in organism.Cells)
            {
                // save the base class in the cells table
                int cellID = int.Parse(_qta.usp_saveCell(cell.GetType().ToString(), cell.Guid.ToString(), Convert.ToInt32(cell.HBTSAlive), Convert.ToInt32(cell.HBTSDead), organismID).ToString());

                // save the type class in their extended table eg neurons, physical cells etc
                SaveOrganismExtendedCells(cellID, cell, synapses, neurons, SynapseIOs);
            }

            // save the synapses(and their learning rules)
            if ((synapses.Count > 0) && (neurons.Count > 0))
            {
                // save the synapses
                SaveSynapses(synapses, neurons, SynapseIOs, organismID);
            }

            // save the neural system
            // todo : simeple neural system doesnt need this at the moment

            // save the dna
            SaveDNA(organism, organismID);

            return organismID;
        }

        /// <summary>
        /// Save the DNA of the organism
        /// </summary>
        private void SaveDNA(IOrganism organism, int organismID)
        {
            // save each of the chromosomes
            foreach (IChromosome chromsome in organism.DNA.Chromosomes)
            {
                // save the chromosome
                int chromsomeID = int.Parse(_qta.usp_saveChromosome(organismID, chromsome.Guid.ToString()).ToString());

                // save the gene within the chromosome
                foreach (IGene gene in chromsome.Genes)
                {
                    // save the base gene
                    int geneID = int.Parse(_qta.usp_saveGene(chromsomeID, gene.GetType().ToString(), gene.ActionType.ToString(), gene.Guid.ToString()).ToString());

                    // save the extended type
                    switch (gene.GetType().ToString())
                    {
                        case GENECELLULAR:
                            int cellID = GetDBCellID(organismID, ((GeneCellular)gene).Cell.Guid);
                            int geneCellularID = int.Parse(_qta.usp_saveGeneCellular(geneID, cellID).ToString());
                            break;
                        case GENEASEXUALREPRODUCTION:
                            int geneReproductive = int.Parse(_qta.usp_saveGeneReproductive(geneID, ((GeneReproductionAsexual)gene).MutationRate).ToString());
                            break;

                        default:
                            throw new NotImplementedException();
                    }

                    // save the requirements
                    SaveRequirements(gene, geneID, organismID);
                }
            }
        }

        /// <summary>
        /// get the cell ID for a particual organism by guid
        /// </summary>
        /// <param name="organismID"></param>
        /// <param name="guid"></param>
        /// <returns></returns>
        private int GetDBCellID(int organismID, Guid guid)
        {
            // get the cell ID from the organism            
            var vocdt = new GridDB.vw_organismCellsDataTable();
            try
            {
                _vocta.FillBy(vocdt, organismID, guid.ToString());
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vocdt, ex);
            }

            return vocdt[0].CellID;
        }

        /// <summary>
        /// get the scenario id
        /// </summary>
        /// <param name="scenarioName">scenario name</param>
        /// <returns></returns>
        private int GetScenarioID(string scenarioName)
        {
            // result
            int result = 0;

            var vsdt = new GridDB.vw_scenariosDataTable();
            try
            {
                _vsta.FillBy(vsdt, scenarioName);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vsdt, ex);
            }

            // scenario id
            try
            {
                result = vsdt[0].ScenarioID;
            }
            catch (IndexOutOfRangeException ex)
            {
                // didnt find it, just return 0                
            }

            WebLogger.LogDebug(string.Format("Scenario Name : {0} has ID: {1}", scenarioName, result));

            return result;
        }

        /// <summary>
        /// save the requirements of the gene
        /// </summary>
        /// <param name="gene">gene to save the requriements</param>
        /// <param name="geneID">gene id save in the db</param>
        private void SaveRequirements(IGene gene, int geneID, int organismID)
        {
            // save each of the requirements
            foreach (IRequirement requirement in gene.Requirements)
            {
                // save the base requirement
                int requirementId = int.Parse(_qta.usp_saveGeneRequirement(requirement.GetType().ToString(), requirement.ComparisonType.ToString(), geneID, requirement.Guid.ToString()).ToString());

                // save the extended requirement
                switch (requirement.GetType().ToString())
                {
                    case REQHBOFFSET:
                        int hbOffset = int.Parse(_qta.usp_saveReqOffsetHB(requirementId, ((HBOffsetTime)requirement).HBOffset).ToString());
                        break;

                    case REQHBOFFSETCELL:
                        int hbOffsetCell = int.Parse(_qta.usp_saveReqOffsetHBCell(requirementId, ((HBOffsetTime)requirement).HBOffset, GetDBCellID(organismID, ((HBOffsetTimeCell)requirement).Cell.Guid)).ToString());
                        break;

                    case REQIOSYNAPSEVALUE:
                        int ioSynapseValue = int.Parse(_qta.usp_saveReqIOSynapseValue(requirementId, ((IOSynapseValue)requirement).Threshold, ((IOSynapseValue)requirement).BindingName).ToString());
                        break;

                    default:
                        throw new NotImplementedException();
                }
            }
        }

        /// <summary>
        /// Save the base class cell 
        /// </summary>
        /// <param name="cellId">parent cell id in the db</param>
        /// <param name="cell">cell to save</param>
        /// <param name="synapses"></param>
        /// <param name="neurons"></param>
        private void SaveOrganismExtendedCells(int cellId, ICell cell, Dictionary<Guid, ISynapse> synapses, Dictionary<Guid, int> neurons, Dictionary<Guid, int> synapseIOs)
        {
            switch (cell.GetType().ToString())
            {
                // Physical cells
                case AMOEBA:
                case IROBOT:
                    // save the physical cell
                    var physicalCellId = int.Parse(_qta.usp_savePhysicalCell(cellId).ToString());
                    // add physical to binding cell
                    foreach (SynapseIO synapseIO in ((IPhysical)cell).IOManifest)
                    {
                        int physicalBindingCellId = int.Parse(_qta.usp_savePhysicalCellBindings(physicalCellId, synapseIO.BindingName, synapseIO.Description, synapseIO.ShortName, synapseIO.Guid.ToString()).ToString());
                        synapseIOs.Add(synapseIO.Guid, physicalBindingCellId);
                    }
                    break;
                case STEPNEURON:
                    // save the neuron
                    INeuron neuron = (INeuron)cell;
                    int neuronCellId = int.Parse(_qta.usp_saveNeuron(cellId, neuron.ThresholdValue, ((CI.Interfaces.ICell)neuron).Guid.ToString()).ToString());
                    // add the neuron to the hash table
                    if (!(neurons.ContainsKey(cell.Guid)))
                        neurons.Add(cell.Guid, neuronCellId);

                    // check all the output neurons
                    foreach (var synapse in ((INeuron)cell).OutputSynapse)
                    {
                        if (!(synapses.ContainsKey(synapse.Guid)))
                            synapses.Add(synapse.Guid, synapse);
                    }

                    // check all the output neurons
                    foreach (var synapse in ((INeuron)cell).InputSynapse)
                    {
                        if (!(synapses.ContainsKey(synapse.Guid)))
                            synapses.Add(synapse.Guid, synapse);
                    }
                    break;
                default:
                    break;
            }
        }

        /// <summary>
        /// save the synapses
        /// </summary>
        /// <param name="neurons">neurons cell ids</param>
        /// <param name="synapses">synapses to save</param>
        /// <param name="organismID">organism ID in the db</param>
        private void SaveSynapses(Dictionary<Guid, ISynapse> synapses, Dictionary<Guid, int> neurons, Dictionary<Guid, int> synapseIOs, int organismID)
        {

            // save each of the syapses
            foreach (KeyValuePair<Guid, ISynapse> kvp in synapses)
            {
                // set the source neuron id
                int? sourceNeuronID = null;
                int? sourceSynapseIOID = null;

                if (kvp.Value.SourceNotification != null)
                {
                    if (neurons.ContainsKey(kvp.Value.SourceNotification.Guid))
                        sourceNeuronID = neurons[kvp.Value.SourceNotification.Guid];
                    else
                        sourceSynapseIOID = synapseIOs[kvp.Value.SourceNotification.Guid];
                }

                //// set the target neuron id
                int? targetNeuronID = null;
                int? targetSynapseIOID = null;

                if (kvp.Value.TargetNotification != null)
                {
                    if (neurons.ContainsKey(kvp.Value.TargetNotification.Guid))
                        targetNeuronID = neurons[kvp.Value.TargetNotification.Guid];
                    else
                        targetSynapseIOID = synapseIOs[kvp.Value.TargetNotification.Guid];
                }

                // save the syanpse
                //int synapseID = int.Parse(_qta.usp_saveSynapse(kvp.Value.GetType().ToString(), kvp.Value.InputWait, kvp.Value.OutputHold, kvp.Value.InitInputWeight, kvp.Value.BindingName, kvp.Value.Guid.ToString(), sourceNeuronID, targetNeuronID, organismID).ToString());
                int synapseID = int.Parse(_qta.usp_saveSynapse(kvp.Value.GetType().ToString(), kvp.Value.InputWait, kvp.Value.OutputHold, kvp.Value.InitInputWeight, kvp.Value.Guid.ToString(), targetNeuronID, sourceNeuronID, targetSynapseIOID, sourceSynapseIOID, organismID).ToString());

                // save the learning rules if it has one
                if (kvp.Value.LearningRule != null)
                {
                    int learningRuleID = int.Parse(_qta.usp_saveLearningRule(kvp.Value.LearningRule.GetType().ToString(), kvp.Value.LearningRule.GainLearningRate, kvp.Value.LearningRule.LossLearningRate, kvp.Value.LearningRule.Guid.ToString(), synapseID).ToString());
                }

            }

        }

        /// <summary>
        /// save the scenario dal
        /// </summary>
        /// <param name="scenarioName">scenario name to save it to</param>
        /// <param name="organisms">list of organisms</param>
        public int SaveDAL(string scenarioName, CI.DAL.DALList organisms, int UserID)
        {

            WebLogger.LogDebug("Save DAL - Start");

            // save org count
            int _organismSaveCount = 0;

            // make sure the scenario exits
            int _scenarioID = GetScenarioID(scenarioName);
            if (_scenarioID == 0)
                return 0;

            WebLogger.LogDebug("Scenario ID:" + _scenarioID);
            WebLogger.LogDebug("Scenario Name:" + scenarioName);
            WebLogger.LogDebug("User ID:" + UserID);
            WebLogger.LogDebug("Start Organisms Count:" + organisms.Count);

            // save the organisms
            foreach (CI.Organism.Organism organism in organisms)
            {
                WebLogger.LogDebug("Saving Organism Guid : " + organism.Guid.ToString());

                try
                {
                    // check and make sure the guid is not already in the db if so dont add it
                    object _result = _qta.usp_findOrgIDByGuid(organism.Guid.ToString(), _scenarioID);

                    WebLogger.LogDebug("Save DAL - Find organism by guid, org id result:" + _result);

                    // if we have result that is not null then the record already exists go continue
                    if (_result != null)
                    {
                        WebLogger.LogDebug("Not saving organism :" + organism.Guid.ToString());
                        continue;
                    }
                }
                catch
                {
                    continue;
                }

                // save the organism
                int organismID = SaveOrganism(organism, scenarioName, UserID);
                WebLogger.LogDebug("Saved Organism Guid :" + organism.Guid.ToString());
                _organismSaveCount++;
            }

            WebLogger.LogDebug("Saved Organisms Count:" + _organismSaveCount);
            WebLogger.LogDebug("Save DAL - Finish");

            return _organismSaveCount;
        }

        #endregion

        #region Load

        /// <summary>
        /// Load the dal
        /// </summary>
        /// <param name="scenarioName">scenario to load it from</param>
        /// <param name="organismCount">number of organism you want, if it is set to 0 it will load all available</param>
        /// <param name="organismsToLoad"></param>
        /// <returns>list of organisms</returns>
        public DALList LoadDAL(string scenarioName, int organismCount, Guid[] organismsToLoad)
        {
            // result
            var result = new CI.DAL.DALList();

            // scenario ID
            int scenarioID = GetScenarioID(scenarioName);
            // if we can find one to load against ditch it
            if (scenarioID == 0)
                return result;

            // if 0, load all
            if ((organismCount == 0) || (organismsToLoad.Count() > 0))
            {
                organismCount = int.Parse(_qta.usp_getScenarioOrganismCount(scenarioID).ToString());
            }

            // scenario
            var scenario = new ScenarioLocalXML();

            // get the organisms
            var results = LoadOrganisms(scenario, scenarioID, false, organismCount, organismsToLoad.ToList());

            // todo : sort by fitest and only return the top x number, possibly do in sql or code

            // convert the types            
            result.AddRange(results.Cast<Organism>());

            WebLogger.LogDebug(string.Format("LoadDAL loaded {0} results", results.Count));

            // return result);
            return result;
        }

        /// <summary>
        /// Get a scenario based on the name passed to it
        /// </summary>
        /// <param name="scenarioName"></param>
        /// <returns></returns>
        public Scenario LoadScenario(string scenarioName)
        {
            // create a new scenario, not using any methods so doesnt matter which one
            Scenario scenario = new ScenarioLocalXML();

            // get the data from the db
            var vsdt = new GridDB.vw_scenariosDataTable();
            try
            {
                _vsta.Fill(vsdt);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vsdt, ex);
            }

            // found the row that we need to use
            int foundRow = -1;

            // find the row
            for (int i = 0; i < vsdt.Rows.Count; i++)
            {
                if (vsdt[i].ScenarioName.Trim().ToLower() == scenarioName.Trim().ToLower())
                {
                    // save the found value
                    foundRow = i;
                    break;
                }
            }

            // if we didnt find anything then exit
            if (foundRow == -1)
                return null;

            // populate the scenario
            scenario.ScenarioName = vsdt[foundRow].ScenarioName.Trim();
            scenario.BoundryXRadiusMax = vsdt[foundRow].BoundryXRadiusMax;
            scenario.BoundryYRadiusMax = vsdt[foundRow].BoundryYRadiusMax;
            scenario.BoundryZRadiusMax = vsdt[foundRow].BoundryZRadiusMax;
            scenario.FitnessFunction = vsdt[foundRow].FitnessFunction;
            scenario.SimulationRestrictions = vsdt[foundRow].SimulationRestrictions;
            scenario.ManifestFile = vsdt[foundRow].ManifestFile.Trim();
            scenario.ScenarioDescription = vsdt[foundRow].ScenarioDescription.Trim();
            scenario.MaxLoadCount = vsdt[foundRow].MaxLoadCount;
            scenario.MaxSaveCount = vsdt[foundRow].MaxSaveCount;

            // load fallback organisms
            LoadScenarioFallBackOrganisms(scenario, vsdt[foundRow].ScenarioID);

            // load required organisms
            LoadScenarioRequiredOrganisms(scenario, vsdt[foundRow].ScenarioID);

            return scenario;
        }

        /// <summary>
        /// Load the scenario required orgs from db
        /// </summary>
        /// <param name="scenario">scenario we are working with</param>
        /// <param name="scenarioID">scenario id in DB</param>
        private void LoadScenarioRequiredOrganisms(IScenario scenario, int scenarioID)
        {
            // load from db
            var vsrldt = new GridDB.vw_scenarioRequirementLocationsDataTable();
            var vrdt = new GridDB.vw_requirementsDataTable();
            try
            {
                _vrta.Fill(vrdt, scenarioID);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vrdt, ex);
            }

            // create a dummy organism and hosting environment to work with
            IHostingEnvironment hostingEnvironment = new MSRBHost(new MockHostingEnvironmentPhysical(), scenario);
            IOrganism organism = new Organism(hostingEnvironment);

            // load the required organisms
            for (int i = 0; i < vrdt.Rows.Count; i++)
            {
                // if it belongs to this scenario then add it to the list
                if (vrdt[i].ScenarioID == scenarioID)
                {
                    IScenarioEntityRequirements requirement = new Scenario.ScenarioEntityRequirements();

                    switch (vrdt[i].OrganismName.Trim())
                    {
                        case AMOEBA:
                            requirement.OrganismType = new Amoeba(organism, Guid.NewGuid());
                            break;

                        case IROBOT:
                            requirement.OrganismType = new iRobot(organism, Guid.NewGuid());
                            break;

                        default:
                            break;
                    }

                    // save fallback setting
                    requirement.UseFallBackOnly = vrdt[i].UseFallBackOnly;

                    // load the reqruiements
                    try
                    {
                        _vsrlta.Fill(vsrldt, vrdt[i].RequirementID);
                    }
                    catch (ConstraintException ex)
                    {
                        // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                        throw new DetailedConstraintException("error filling table", vsrldt, ex);
                    }

                    // save locations for this requirment
                    for (int j = 0; j < vsrldt.Rows.Count; j++)
                    {
                        // if its the location that matches the requirment that we are working on then save it
                        if (vsrldt[j].RequirementID == vrdt[i].RequirementID)
                        {
                            requirement.Locations.Add(new Location(vsrldt[j].X, vsrldt[j].Y, vsrldt[j].Z, vsrldt[j].XRadius, vsrldt[j].YRadius, vsrldt[j].ZRadius));
                        }
                    }

                    scenario.RequiredOrganisms.Add(requirement);
                }
            }
        }

        /// <summary>
        /// Load the scenario fall back organisms
        /// </summary>
        /// <param name="scenario">scenario we are working with</param>
        /// <param name="scenarioID">scenario id in DB</param>
        private void LoadScenarioFallBackOrganisms(IScenario scenario, int scenarioID)
        {
            // load the scenario
            scenario.FallBackOrganisms = LoadOrganisms(scenario, scenarioID, true, 0, new List<Guid>());
        }

        /// <summary>
        /// Load the organisms
        /// </summary>
        /// <returns></returns>
        public List<IOrganism> LoadOrganisms(IScenario scenario, int scenarioID, bool fallbackOnly, int organismCount, List<Guid> organismsToLoad)
        {
            // results
            var results = new List<IOrganism>();
            var swLoadTime = new Stopwatch();
            swLoadTime.Start();

            // scenario organisms data table
            var vsodt = new GridDB.vw_scenarioOrganismsDataTable();
            try
            {
                if (fallbackOnly)
                    _vsota.FillFallbackOrganisms(vsodt, scenarioID);
                else
                    _vsota.FillByDALOrganisms(vsodt, organismCount, scenarioID);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vsodt, ex);
            }

            swLoadTime.Stop();
            WebLogger.LogDebug("Time taken to load organisms :" + swLoadTime.Elapsed);

            // create a dummy organism and hosting environment to work with
            IHostingEnvironment hostingEnvironment = new MSRBHost(new MockHostingEnvironmentPhysical(), scenario);

            // go through each of the organisms
            for (int i = 0; i < vsodt.Rows.Count; i++)
            {
                var guid = new Guid(vsodt[i].GUID.Trim());

                if (organismsToLoad.Count > 0)
                {
                    if (!organismsToLoad.Contains(guid))
                        continue;
                }

                // create the organism
                IOrganism organism = new Organism(hostingEnvironment)
                                         {
                                             CIET = vsodt[i].CIET,
                                             InitCIET = vsodt[i].InitCIET,
                                             InitEnergy = vsodt[i].InitEnergy,
                                             Guid = guid,
                                             Energy = vsodt[i].Energy,
                                             FitnessValue = vsodt[i].Fitness
                                         };

                // load the location info
                var vldt = new GridDB.vw_locationDataTable();
                try
                {
                    _vlta.Fill(vldt, vsodt[i].LocationID);
                }
                catch (ConstraintException ex)
                {
                    // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                    throw new DetailedConstraintException("error filling table", vsodt, ex);
                }

                // todo : check make sure location is found and not null
                // load the location data
                organism.Location = new Location(vldt[0].X, vldt[0].Y, vldt[0].Z, vsodt[i].XRadius, vsodt[i].YRadius, vsodt[i].ZRadius);

                // set the neural system
                switch (vsodt[i].NeuralSystemName.Trim())
                {
                    case SNS:
                        organism.NeuralSystem = new SimpleNeuralSystem(organism);
                        break;
                    case "NULL":
                        break;
                    default:
                        break;
                }

                // load the cells
                organism.Cells = LoadOrganismCells(vsodt[i].OrganismID, organism);

                // load the dna
                LoadDNA(organism, vsodt[i].OrganismID);

                // add it to the list or organisms
                results.Add(organism);
            }
            return results;
        }

        /// <summary>
        /// Load the requirements of the gene
        /// </summary>
        /// <param name="gene">gene to save the requriements</param>
        /// <param name="geneID">gene id save in the db</param>
        /// <param name="organism"></param>
        private void LoadRequirements(IGene gene, int geneID, IOrganism organism)
        {
            // create the empty requirements collection
            gene.Requirements = new RequirementCollection();

            // load each of the requirements
            var vgrdt = new GridDB.vw_geneRequirementsDataTable();
            try
            {
                _vgrta.Fill(vgrdt, geneID);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vgrdt, ex);
            }

            // cycle through each of the chromosomes
            for (int i = 0; i < vgrdt.Rows.Count; i++)
            {
                // find out the comparison type
                ComparisonType compType = ComparisonType.Unknown;

                switch (vgrdt[i].GeneRequirementComparisonTypeName.Trim())
                {
                    case CTEQUAL:
                        compType = ComparisonType.Equal;
                        break;
                    case CTGT:
                        compType = ComparisonType.GreaterThan;
                        break;
                    case CTLT:
                        compType = ComparisonType.LessThan;
                        break;
                    case CTUNKNOWN:
                        compType = ComparisonType.Unknown;
                        break;
                    default:
                        throw new NotImplementedException();
                }

                // requirement
                IRequirement requirement;

                switch (vgrdt[i].GeneRequirementTypeName.Trim())
                {
                    case REQHBOFFSET:                                           // todo : fix conversion
                        requirement = new HBOffsetTime(organism, Convert.ToUInt32(vgrdt[i].ReqHBOffset), compType, new Guid(vgrdt[i].ReqGuid.Trim()));
                        break;
                    case REQHBOFFSETCELL:                                           // todo : fix conversion
                        requirement = new HBOffsetTimeCell(organism, Convert.ToUInt32(vgrdt[i].ReqHBOffsetCells), organism.Cells.Find(new Guid(vgrdt[i].CellGuid.Trim())), compType, new Guid(vgrdt[i].ReqGuid.Trim()));
                        break;
                    case REQIOSYNAPSEVALUE:
                        requirement = new IOSynapseValue(vgrdt[i].BindingName.Trim(), vgrdt[i].ThresholdValue, organism, compType, new Guid(vgrdt[i].ReqGuid.Trim()));
                        break;
                    default:
                        throw new NotImplementedException();
                }

                gene.Requirements.Add(requirement);
            }

        }

        /// <summary>
        /// Load the DNA of the organism
        /// </summary>
        private void LoadDNA(IOrganism organism, int organismID)
        {
            // create the dna
            organism.DNA = new DNA { Chromosomes = new ChromosomeCollection(), ActioningGenes = new GeneCollection() };
            // create the chromsome collection
            // create the actioning genes

            // load each of the chromosomes
            var vcdt = new GridDB.vw_chromosomesDataTable();
            try
            {
                _vcta.Fill(vcdt, organismID);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vcdt, ex);
            }

            // cycle through each of the chromosomes
            for (int i = 0; i < vcdt.Rows.Count; i++)
            {
                // create the chromsome
                IChromosome chromsome = new Chromosome(new Guid(vcdt[i].GUID.Trim())) { Genes = new GeneCollection() };

                // add it to the dna
                organism.DNA.Chromosomes.Add(chromsome);

                // add the genes
                var vgdt = new GridDB.vw_genesDataTable();
                try
                {
                    _vgta.Fill(vgdt, vcdt[i].ChromsomeID);
                }
                catch (ConstraintException ex)
                {
                    // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                    throw new DetailedConstraintException("error filling table", vgdt, ex);
                }

                // cycle through each of the genes
                for (int j = 0; j < vgdt.Rows.Count; j++)
                {
                    IGene gene;

                    // create the gene
                    switch (vgdt[j].GeneTypeName.Trim())
                    {
                        case GENECELLULAR:
                            gene = new GeneCellular(organism, new Guid(vgdt[j].GUID.Trim()));
                            ((GeneCellular)gene).Cell = organism.Cells.Find(new Guid(vgdt[j].CellularGeneGuid.Trim()));
                            break;
                        case GENEASEXUALREPRODUCTION:
                            gene = new GeneReproductionAsexual(organism, new Guid(vgdt[j].GUID.Trim()));
                            ((GeneReproductionAsexual)gene).MutationRate = vgdt[j].MutationRate;
                            break;
                        default:
                            throw new NotImplementedException();
                    }

                    // get the action type
                    switch (vgdt[j].ActionTypeNames.Trim())
                    {
                        case ATADDCELL:
                            gene.ActionType = ActionType.AddCell;
                            break;
                        case ATDELCELL:
                            gene.ActionType = ActionType.DelCell;
                            break;
                        case ATREMCELL:
                            gene.ActionType = ActionType.RemoveCell;
                            break;
                        case ATUNKNOWN:
                            gene.ActionType = ActionType.Unknown;
                            break;
                        case ATNOTAPP:
                            gene.ActionType = ActionType.NotApplicable;
                            break;
                        default:
                            throw new NotImplementedException();
                    }

                    // add the gene
                    chromsome.Genes.Add(gene);

                    // add its requirements
                    LoadRequirements(gene, vgdt[j].GeneID, organism);
                }
            }

        }

        /// <summary>
        /// Load the cells given a organism id in a the db
        /// </summary>
        /// <param name="organismID">organism id in the db</param>
        /// <param name="organism"></param>
        /// <returns>cell collection</returns>
        private ICellCollection LoadOrganismCells(int organismID, IOrganism organism)
        {
            // get a list of the cells
            ICellCollection cells = new CellCollection();

            // organisms cells data table
            var vocdt = new GridDB.vw_organismCellsDataTable();
            try
            {
                _vocta.Fill(vocdt, organismID);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vocdt, ex);
            }

            // cycle through each of the cells
            for (int i = 0; i < vocdt.Rows.Count; i++)
            {
                switch (vocdt[i].CellTypeName.Trim())
                {
                    case AMOEBA:
                        cells.Add(new Amoeba(organism, new Guid(vocdt[i].GUID.Trim())));
                        break;

                    case IROBOT:
                        cells.Add(new iRobot(organism, new Guid(vocdt[i].GUID.Trim())));
                        break;

                    case STEPNEURON:
                        // add the cells
                        cells.Add(new StepNeuron(vocdt[i].ThresholdValue, organism.NeuralSystem, new Guid(vocdt[i].GUID.Trim())));
                        break;

                    default:
                        throw new ScenarioException("Unknown cell type : " + vocdt[i].CellTypeName.Trim());
                }

            }

            // add the synapses
            LoadSynapses(cells, organism, organismID);

            return cells;
        }

        /// <summary>
        /// Load the synapses for the neuron cells
        /// </summary>
        /// <param name="cells">cell collection to add the synapses to</param>
        /// <param name="organism">organism that the cells belong to</param>
        /// <param name="organismID">organism ID in the db</param>
        private void LoadSynapses(ICellCollection cells, IOrganism organism, int organismID)
        {
            // organisms synapses data table
            var vsyndt = new GridDB.vw_synapsesDataTable();
            vsyndt.LearningRuleNameColumn.AllowDBNull = true;
            vsyndt.LearningRuleGUIDColumn.AllowDBNull = true;
            vsyndt.TargetNeuronGUIDColumn.AllowDBNull = true;
            vsyndt.SourceNeuronGUIDColumn.AllowDBNull = true;
            vsyndt.SourceSyanpseIOGUIDColumn.AllowDBNull = true;
            vsyndt.TargetSyanpseIOGUIDColumn.AllowDBNull = true;
            try
            {
                _vsynta.Fill(vsyndt, organismID);
            }
            catch (ConstraintException ex)
            {
                // pass the DataTable to DetailedConstraintException to get a more detailed Message property
                throw new DetailedConstraintException("error filling table", vsyndt, ex);
            }

            // cycle through each of the synapses
            for (int i = 0; i < vsyndt.Rows.Count; i++)
            {
                // create the synapse
                ISynapse synapse;

                switch (vsyndt[i].SynapseTypeName.Trim())
                {
                    case SIMPLESYNAPSE:
                        synapse = new SimpleSynapse(new Guid(vsyndt[i].GUID), organism.NeuralSystem);
                        break;

                    default:
                        throw new ScenarioException("Unknown Synapse type : " + vsyndt[i].SynapseTypeName);
                }

                synapse.InitInputWeight = vsyndt[i].InitInputWeight;
                synapse.InputWait = vsyndt[i].InputWait;
                synapse.OutputHold = vsyndt[i].OutputHold;

                if (!vsyndt[i].IsSourceNeuronGUIDNull())
                    LoadSynapseSourceNeuron(vsyndt, i, synapse, cells);
                if (!vsyndt[i].IsTargetNeuronGUIDNull())
                    LoadSynapseTargetNeuron(vsyndt, i, synapse, cells);

                if (!vsyndt[i].IsSourceSyanpseIOGUIDNull())
                {
                    synapse.AddSynapticConnectionSource(organism.GetSynapseIO(new Guid(vsyndt[i].SourceSyanpseIOGUID.Trim())));
                }

                if (!vsyndt[i].IsTargetSyanpseIOGUIDNull())
                {
                    synapse.AddSynapticConnectionTarget(organism.GetSynapseIO(new Guid(vsyndt[i].TargetSyanpseIOGUID.Trim())));
                }


                if (!vsyndt[i].IsLearningRuleGUIDNull())
                    LoadSynapseLearningRule(vsyndt, i, synapse);
            }
        }

        private static void LoadSynapseTargetNeuron(GridDB.vw_synapsesDataTable vsyndt, int i, ISynapse synapse, ICellCollection cells)
        {
            try
            {
                if (!string.IsNullOrEmpty(vsyndt[i].TargetNeuronGUID))
                {
                    var neuron = (INeuron)cells.Find(new Guid(vsyndt[i].TargetNeuronGUID.Trim()));
                    if (neuron != null)
                    {
                        synapse.AddSynapticConnectionTarget(neuron);
                        neuron.InputSynapse.Add(synapse);
                    }
                }
            }
            catch (StrongTypingException ste)
            {
                // log it
                WebLogger.LogException("Loading Synapse Target Neuron", ste);
            }
        }

        private static void LoadSynapseSourceNeuron(GridDB.vw_synapsesDataTable vsyndt, int i, ISynapse synapse, ICellCollection cells)
        {
            try
            {
                if (!string.IsNullOrEmpty(vsyndt[i].SourceNeuronGUID))
                {
                    var neuron = (INeuron)cells.Find(new Guid(vsyndt[i].SourceNeuronGUID.Trim()));
                    if (neuron != null)
                    {
                        synapse.AddSynapticConnectionSource(neuron);
                        neuron.OutputSynapse.Add(synapse);
                    }
                }
            }
            catch (StrongTypingException ste)
            {
                // log it
                WebLogger.LogException("Loading Synapse Source Neuron", ste);
            }
        }

        private static void LoadSynapseLearningRule(GridDB.vw_synapsesDataTable vsyndt, int i, ISynapse synapse)
        {
            try
            {
                // load the learning rule if it exists
                if (!string.IsNullOrEmpty(vsyndt[i].LearningRuleName))
                {
                    // learning rule
                    ILearningRule learningRule;

                    switch (vsyndt[i].LearningRuleName.Trim())
                    {
                        case USEITORLOSEIT:
                            learningRule = new UseItOrLoseIt(vsyndt[i].GainLearningRate, vsyndt[i].LossLearningRate, synapse, new Guid(vsyndt[i].LearningRuleGUID.Trim()));
                            break;

                        default:
                            throw new ScenarioException("Unknown learning rule type : " + vsyndt[i].LearningRuleName.Trim());
                    }

                    synapse.LearningRule = learningRule;
                }
            }
            catch (StrongTypingException ste)
            {
                // log it
                WebLogger.LogException("Loading Synapse Learning Rule", ste);
            }
        }

        #endregion

        /// <summary>Scenario Exception</summary>
        public class ScenarioException : Exception
        {
            /// <summary>
            /// Synapse Exception
            /// </summary>
            /// <param name="message">Exception Message</param>
            public ScenarioException(string message)
                : base(message)
            {

            }
        }

        /// <summary>
        /// Subclass of ConstraintException that explains row and column errors in the Message property
        /// Thanks to Jonathan Webb
        /// http://stackoverflow.com/questions/37936/handling-xsd-dataset-constraintexceptions
        /// </summary>
        private class DetailedConstraintException : ConstraintException
        {
            private const int INITIAL_COUNT_VALUE = 1;

            /// <summary>
            /// Initialises a new instance of DetailedConstraintException with the specified string and DataTable
            /// </summary>
            /// <param name="message">exception message</param>
            /// <param name="ErroredTable">DataTable in error</param>
            public DetailedConstraintException(string message, DataTable erroredTable)
                : base(message)
            {
                ErroredTable = erroredTable;
            }


            /// <summary>
            /// Initialises a new instance of DetailedConstraintException with the specified string, DataTable and inner Exception
            /// </summary>
            /// <param name="message">exception message</param>
            /// <param name="erroredTable">DataTable in error</param>
            /// <param name="inner">the original exception</param>
            public DetailedConstraintException(string message, DataTable erroredTable, Exception inner)
                : base(message, inner)
            {
                ErroredTable = erroredTable;
            }


            private string BuildErrorSummaryMessage()
            {
                if (null == ErroredTable) { return "No errored DataTable specified"; }
                if (!ErroredTable.HasErrors) { return "No Row Errors reported in DataTable=[" + ErroredTable.TableName + "]"; }

                foreach (DataRow row in ErroredTable.GetErrors())
                {
                    RecordColumnsInError(row);
                    RecordRowsInError(row);
                }

                var sb = new StringBuilder();

                AppendSummaryIntro(sb);
                AppendErroredColumns(sb);
                AppendRowErrors(sb);

                return sb.ToString();
            }


            private void RecordColumnsInError(DataRow row)
            {
                foreach (DataColumn column in row.GetColumnsInError())
                {
                    if (_erroredColumns.ContainsKey(column.ColumnName))
                    {
                        _erroredColumns[column.ColumnName]++;
                        continue;
                    }

                    _erroredColumns.Add(column.ColumnName, INITIAL_COUNT_VALUE);
                }
            }


            private void RecordRowsInError(DataRow row)
            {
                if (_rowErrors.ContainsKey(row.RowError))
                {
                    _rowErrors[row.RowError]++;
                    return;
                }

                _rowErrors.Add(row.RowError, INITIAL_COUNT_VALUE);
            }


            private void AppendSummaryIntro(StringBuilder sb)
            {
                sb.AppendFormat("Errors reported for {1} [{2}]{0}", Environment.NewLine, ErroredTable.GetType().FullName, ErroredTable.TableName);
            }


            private void AppendErroredColumns(StringBuilder sb)
            {
                sb.AppendFormat("Columns in error: [{1}]{0}", Environment.NewLine, _erroredColumns.Count);

                foreach (string columnName in _erroredColumns.Keys)
                {
                    sb.AppendFormat("\t[{1}] - rows affected: {2}{0}",
                                    Environment.NewLine,
                                    columnName,
                                    _erroredColumns[columnName]);
                }
            }


            private void AppendRowErrors(StringBuilder sb)
            {
                sb.AppendFormat("Row errors: [{1}]{0}", Environment.NewLine, _rowErrors.Count);

                foreach (string rowError in _rowErrors.Keys)
                {
                    sb.AppendFormat("\t[{1}] - rows affected: {2}{0}",
                                    Environment.NewLine,
                                    rowError,
                                    _rowErrors[rowError]);
                }
            }


            /// <summary>
            /// Get the DataTable in error
            /// </summary>
            public DataTable ErroredTable
            {
                get { return _erroredTable; }
                private set { _erroredTable = value; }
            }


            /// <summary>
            /// Get the original ConstraintException message with extra error information
            /// </summary>
            public override string Message
            {
                get { return base.Message + Environment.NewLine + BuildErrorSummaryMessage(); }
            }


            private readonly SortedDictionary<string, int> _rowErrors = new SortedDictionary<string, int>();
            private readonly SortedDictionary<string, int> _erroredColumns = new SortedDictionary<string, int>();
            private DataTable _erroredTable;
        }
    }



}
